﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Xml;


namespace RSSReader.classesCustom
{
    public class DataHandler
    {
        private DataHandlerTimer timer;
        private List<Article> xmlArticles;
        public List<Article> onlineArticles { get { return xmlArticles; } }

        private List<Site> rssSites;
        public List<Site> webSites { get { return rssSites; } }

        private SqlConnection dataConnection;
        private SqlDataReader rdr;


        /// <summary>
        /// constructor
        /// calls the constructor when no conection string is set
        /// using a default connection string
        /// </summary>
        public DataHandler()                                                    //kaaReaderSomeeConnectionString
            : this(System.Configuration.ConfigurationManager.ConnectionStrings["kaaReaderSilcoConnectionString"].ConnectionString)
        {
        }

        /// <summary>
        /// constructor
        /// creates database connection object , which is freed up only on object release
        /// loads available site list and online article list
        /// </summary>
        /// <param name="connectionString">
        /// </param>       
        public DataHandler(String connectionString)
        {
            dataConnection = new SqlConnection(connectionString);
            loadRssSites();
            timer = new DataHandlerTimer(this);            
        }

        /// <summary>
        /// loads available(browseable) websites in memory
        /// </summary>
        private void loadRssSites()
        {
            Site site = null;

            rssSites = new List<Site>();

            using (SqlCommand cmd = new SqlCommand("SELECT [ID],[Name],[RSSAddress],[Description],[Logo] FROM [sites]", dataConnection))
            {
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        site = new Site();

                        site.ID = (Int32)rdr["ID"];
                        site.name = (String)rdr["Name"];
                        site.rssAddress = (String)rdr["RSSAddress"];
                        site.description = (String)rdr["Description"];
                        site.logo = (String)rdr["Logo"];
                        site.visible = false;
                        rssSites.Add(site);
                    }
                }
            }
        }

        /// <summary>
        /// returns the list of siteID-s which should be shown for
        /// the user with ID= 'userID'
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<Int32> getUserVisibleSiteIndexes(Int32 userID)
        {
            rdr = null;
            List<Int32> visibleSiteIDs = new List<Int32>();

            using (SqlCommand cmd = new SqlCommand(
                "SELECT siteID FROM [userSites] WHERE userID=@currentUser", dataConnection))
            {
                cmd.Parameters.AddWithValue("@currentUser", userID);
                using (rdr = cmd.ExecuteReader())
                    while (rdr.Read())
                        visibleSiteIDs.Add((Int32)rdr["siteID"]);
            }
            return visibleSiteIDs;
        }

        /// <summary>
        /// loads RSS feeds into local list made available through property to all users
        /// </summary>
        public void loadXMLArticles()
        {
            Article article;
            xmlArticles = new List<Article>();

            int idCount = 0;
            foreach (Site site in rssSites)
            {
                XmlDocument xmlDoc = new XmlDocument();
                try
                {
                    xmlDoc.Load(site.rssAddress);
                    XmlNodeList itemNodes = xmlDoc.SelectNodes("//rss/channel/item");
                    foreach (XmlNode itemNode in itemNodes)
                    {
                        article = new Article();
                        article.siteID = site.ID;
                        article.logo = site.logo;
                        try
                        {
                            article.category = itemNode.SelectSingleNode("category").InnerText;
                        }
                        catch (Exception)
                        {
                            article.category = "";
                        }
                        article.ID = idCount++;
                        article.title = itemNode.SelectSingleNode("title").InnerText;
                        article.pubdate = Convert.ToDateTime(itemNode.SelectSingleNode("pubDate").InnerText);
                        article.description = itemNode.SelectSingleNode("description").InnerText;
                        article.fullArticle = "This is a test of full article... needs implementation !!!!";
                        try
                        {
                            article.weblink = itemNode.SelectSingleNode("guid").InnerText;
                        }
                        catch (Exception)
                        {
                            try
                            {
                                article.weblink = itemNode.SelectSingleNode("link").InnerText;
                            }
                            catch (Exception)
                            {
                                article.weblink = "Unable to get link";
                            }
                        }

                        xmlArticles.Add(article);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("Unable to load from " + site.rssAddress);
                    Console.WriteLine(e.StackTrace);
                }
            }
        }
        
        /// <summary>
        /// saves to database the article with id "articleID" 
        /// referenced by the user with id "userID"
        /// </summary>
        /// <param name="articleID"></param>
        /// <param name="userID"></param>
        public void saveXMLArticleToDatabase(int articleID, Int32 userID)
        {
            using (SqlCommand cmd = new SqlCommand("insertArticleforUser", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@userID", @userID);
                cmd.Parameters.AddWithValue("@siteID", xmlArticles[articleID].siteID);
                cmd.Parameters.AddWithValue("@title", xmlArticles[articleID].title);
                cmd.Parameters.AddWithValue("@category", xmlArticles[articleID].category);
                cmd.Parameters.AddWithValue("@description", xmlArticles[articleID].description);
                cmd.Parameters.AddWithValue("@fullarticle", xmlArticles[articleID].fullArticle);
                cmd.Parameters.AddWithValue("@weblink", xmlArticles[articleID].weblink);
                cmd.Parameters.AddWithValue("@pubdate", xmlArticles[articleID].pubdate);
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                try
                {
                    cmd.ExecuteNonQuery();

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    //TODO: RETURN ERROR message IF NOT SUCCEEDED!!!
                }
            }
        }


        /// <summary>
        /// returns result for objectdatasource 
        /// </summary>
        /// <returns>
        /// a complete List with Article-s saved to database
        /// </returns>
        public List<Article> getSQLArticles()
        {
            rdr = null;
            Article article = null;
            List<Article> articleList = new List<Article>();
            // create a command object            
            using (SqlCommand cmd = new SqlCommand(
                "SELECT wa.ID,wa.siteID,ws.Logo,wa.Title,wa.Category," +
                    "wa.Description,wa.Fullarticle,wa.Weblink,wa.Pubdate " +
                "FROM [articles] wa JOIN [sites] ws ON wa.siteID=ws.ID ", dataConnection))
            {

                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                // 1. get an instance of the SqlDataReader                
                using (rdr = cmd.ExecuteReader())
                {
                    // 2. convert each record
                    while (rdr.Read())
                    {
                        article = new Article();

                        article.ID = (Int32)rdr["ID"];
                        article.siteID = (Int32)rdr["siteID"];
                        article.logo = (String)rdr["logo"];
                        article.title = (String)rdr["Title"];
                        article.category = (String)rdr["Category"];
                        article.description = (String)rdr["Description"];
                        article.fullArticle = (String)rdr["Fullarticle"];
                        article.weblink = (String)rdr["Weblink"];
                        article.pubdate = (DateTime)rdr["Pubdate"];

                        articleList.Add(article);
                    }
                }
            }

            return articleList;
        }
        
        /// <summary>
        /// returns paged result for objectdatasource 
        /// needed for objectdatasource custom paging
        /// </summary>
        /// <returns>
        /// List with Article-s (one page) saved to database
        /// </returns>
        public List<Article> getSQLArticles(int startRowIndex, int maximumRows)
        {
            Int32 recordCountSQL;
            rdr = null;
            Article article = null;
            List<Article> articleList = new List<Article>();
            // create a command object   
            using (SqlCommand cmd = new SqlCommand("dbo.getPartOfWebarticles", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // set up the parameters
                cmd.Parameters.Add("@fromRecord", SqlDbType.Int);
                cmd.Parameters.Add("@toRecord", SqlDbType.Int);
                cmd.Parameters.Add("@totalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

                // set parameter values
                cmd.Parameters["@fromRecord"].Value = startRowIndex;
                cmd.Parameters["@toRecord"].Value = startRowIndex + maximumRows;

                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                // 1. get an instance of the SqlDataReader
                using (rdr = cmd.ExecuteReader())
                {
                    recordCountSQL = Convert.ToInt32(cmd.Parameters["@totalRecords"].Value);
                    // 2. convert each record
                    while (rdr.Read())
                    {
                        article = new Article();

                        article.ID = (Int32)rdr["ID"];
                        article.siteID = (Int32)rdr["siteID"];
                        article.logo = (String)rdr["logo"];
                        article.title = (String)rdr["Title"];
                        article.category = (String)rdr["Category"];
                        article.description = (String)rdr["Description"];
                        article.fullArticle = (String)rdr["Fullarticle"];
                        article.weblink = (String)rdr["Weblink"];
                        article.pubdate = (DateTime)rdr["Pubdate"];

                        articleList.Add(article);
                    }
                }

            }
            return articleList;
        }       

        /// <summary>
        /// returns from database values requested 
        /// </summary>
        /// <param name="startRowIndex"> articles starting from position</param>
        /// <param name="maximumRows">number of articles needed (one page)</param>
        /// <param name="userID"></param>
        /// <returns>
        /// one page of customized list with Article-s available for user
        /// total number of records available in customized list
        /// </returns>
        public KeyValuePair<Int32, List<Article>> getUserCustomSQLArticles(int startRowIndex, int maximumRows, Int32 userID)
        {
            Int32 recordCountSQL;
            Article article;
            List<Article> articleList = new List<Article>();

            using (SqlCommand cmd = new SqlCommand("getVisiblePagedFilteredArticlesforUser", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@fromRecord", startRowIndex+1));
                cmd.Parameters.Add(new SqlParameter("@toRecord", startRowIndex+maximumRows));
                cmd.Parameters.Add(new SqlParameter("@userId", userID));
                cmd.Parameters.Add(new SqlParameter("@artTitle", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@artCategory", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@artDescription", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@artFullarticle", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@startPubDate", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@endPubDate", DBNull.Value));
                cmd.Parameters.Add("@totalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }

                // 1. get an instance of the SqlDataReader
                using (rdr = cmd.ExecuteReader())
                {                    
                    // 2. convert each record
                    while (rdr.Read())
                    {
                        article = new Article();

                        article.ID = (Int32)rdr["ID"];
                        article.siteID = (Int32)rdr["siteID"];
                        article.logo = (String)rdr["logo"];
                        article.title = (String)rdr["Title"];
                        article.category = (String)rdr["Category"];
                        article.description = (String)rdr["Description"];
                        article.fullArticle = (String)rdr["Fullarticle"];
                        article.weblink = (String)rdr["Weblink"];
                        article.pubdate = (DateTime)rdr["Pubdate"];

                        articleList.Add(article);
                    }                     
                }
                recordCountSQL = Convert.ToInt32(cmd.Parameters["@totalRecords"].Value);                   
            }
            return new  KeyValuePair<Int32, List<Article>>(recordCountSQL,articleList);
        }

        /// <summary>
        /// Retrievies article from database based on the articleID 
        /// </summary>
        /// <param name="articleID"></param>
        /// <returns></returns>
        public Article getSQLArticlebyId(Int32 articleID)
        {
            Article article = new Article();
            
            // create a command object   
            using (SqlCommand cmd = new SqlCommand("dbo.getArticlebyId", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // set up the parameters
                cmd.Parameters.Add(new SqlParameter("@articleID", articleID));
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                // 1. get an instance of the SqlDataReader
                using (rdr = cmd.ExecuteReader())
                {
                    // 2. convert record
                    if (rdr.Read())
                    {
                        article.ID = (Int32)rdr["ID"];
                        article.siteID = (Int32)rdr["siteID"];
                        article.logo = (String)rdr["logo"];
                        article.title = (String)rdr["Title"];
                        article.category = (String)rdr["Category"];
                        article.description = (String)rdr["Description"];
                        article.fullArticle = (String)rdr["Fullarticle"];
                        article.weblink = (String)rdr["Weblink"];
                        article.pubdate = (DateTime)rdr["Pubdate"];
                    }
                }
            }
            return article;
        }

        public void deleteSQLArticleforUser(int articleID, Int32 userID)
        {
            // create a command object   
            using (SqlCommand cmd = new SqlCommand("dbo.deleteArticleforUser", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // set up the parameters
                cmd.Parameters.Add(new SqlParameter("@articleID", articleID));
                cmd.Parameters.Add(new SqlParameter("@userID", userID));
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                cmd.ExecuteNonQuery();                
            }            
        }


        /// <summary>
        /// modifies in database visibility settings for a certain site and user
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="siteID"></param>
        /// <param name="visibility"></param>
        public void setSiteVisibilityforUser(int userID, int siteID, bool visibility)
        {
            using (SqlCommand cmd = new SqlCommand("setSiteVisibilityforUser", dataConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@userID", userID));
                cmd.Parameters.Add(new SqlParameter("@siteID", siteID));
                cmd.Parameters.Add(new SqlParameter("@visibility", visibility));
                // open the connection
                if (dataConnection.State == ConnectionState.Closed)
                {
                    dataConnection.Open();
                }
                cmd.ExecuteNonQuery();
            }
        }
    }
}

// DEPRECATED: !!!!
///// <summary>
///// saves the selected online article to database using id
///// </summary>
///// <param name="id">
///// the online article id which is (should be) the same
///// with article index in List
///// </param>
///// <returns>
///// the new artoicle record's database ID
///// </returns>
//public Int32 saveXMLArticleToDatabase(int id) 
//{
//    SqlParameter paramID;
//    //TODO: check isf article already exist !?!


//    // open the connection
//    if (dataConnection.State != ConnectionState.Open)
//    {
//        dataConnection.Open();
//    }

//     using (SqlCommand cmd = new SqlCommand(
//        "INSERT INTO [webarticles]([siteID],[Title],[Category],[Description]," +
//            "[Fullarticle],[Weblink],[Pubdate]) VALUES (@varSiteID, @varTitle, " +
//            "@varCategory, @varDescription, @varFullarticle, @varWeblink, " +
//            "CONVERT(DATETIME,@varPubdate,103));"+
//        "SELECT SCOPE_IDENTITY()", dataConnection))
//    {
//        cmd.Parameters.AddWithValue("@varSiteID", xmlArticles[id].siteID);
//        cmd.Parameters.AddWithValue("@varTitle", xmlArticles[id].title);
//        cmd.Parameters.AddWithValue("@varCategory", xmlArticles[id].category);
//        cmd.Parameters.AddWithValue("@varDescription", xmlArticles[id].description);
//        cmd.Parameters.AddWithValue("@varFullarticle", xmlArticles[id].fullArticle);
//        cmd.Parameters.AddWithValue("@varWeblink", xmlArticles[id].weblink);
//        cmd.Parameters.AddWithValue("@varPubdate", xmlArticles[id].pubdate);
//        //paramID = new SqlParameter("@ID", SqlDbType.Int, 4);
//        //paramID.Direction = ParameterDirection.Output;
//        //cmd.Parameters.Add(paramID);

//        //cmd.ExecuteNonQuery();

//        int did = (int)(decimal)cmd.ExecuteScalar();
//        return did;
//    }            
//}

///// <summary>
///// 
///// </summary>
///// <param name="articleID"></param>
///// <param name="userID"></param>
//public Int32 saveUserArticleReference(int articleID, Int32 userID)
//{    
//    // TODO: this should be in a transaction!!!
//    // TODO: design stored procedures to use transaction !

//    // open the connection
//    if (dataConnection.State != ConnectionState.Open)
//    {
//        dataConnection.Open();
//    }

//    using (SqlCommand cmd = new SqlCommand(
//        "INSERT INTO [userArticles]([userID],[articleID]) "+ 
//            "VALUES (@uID, @aID)", dataConnection))
//    {
//        cmd.Parameters.AddWithValue("@uID", userID);
//        cmd.Parameters.AddWithValue("@aID", articleID);
//        SqlParameter paramID = new SqlParameter("@ID", SqlDbType.Int, 4);
//        paramID.Direction = ParameterDirection.Output;
//        cmd.Parameters.Add(paramID);

//        cmd.ExecuteNonQuery();

//        return (Int32)cmd.Parameters["@ID"].Value;
//    }            
//}